﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;


namespace WcfServiceCityDashBoard
{
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service1" in both code and config file together.
    public class Service1 : IService1
    {
     
        //CitydashboardModelDataContext data1 = new CitydashboardModelDataContext();
        DataCityDashBoardDataContext data1 = new DataCityDashBoardDataContext();
        //class ViewTTTongQuatHPT
        public List<ViewTTTongQuatHPT_entry> getViewTTTongQuatHPT()
        {
            var TTTongQuat = data1.ViewTTTongQuatHPTs;
            var Emplist = new List<ViewTTTongQuatHPT_entry>();
     
            foreach (var data in TTTongQuat.ToList())
            {

                ViewTTTongQuatHPT_entry GetEmp = new ViewTTTongQuatHPT_entry();
               
                GetEmp.Id = (int)data.Id;
                GetEmp.loai_hs = (string)data.loai_hs;
                GetEmp.percent_soluong_dunghan = (string)data.percent_soluong_dunghan;
                GetEmp.percent_soluong_trehan = (string)data.percent_soluong_trehan;
                GetEmp.soluong_dunghan = (int)data.soluong_dunghan;
                GetEmp.soluong_trehan = (int)data.soluong_trehan;
                GetEmp.soluong_tt = (int)data.soluong_tt;

                Emplist.Add(GetEmp);

            }
            return Emplist;

        }

        //class View_LoaiHS_trinhUB_DungHan_HPT
        public List<View_LoaiHS_trinhUB_DungHan_HPT_entry> getView_LoaiHS_trinhUB_DungHan_HPT()
        {
            var View_LoaiHS_trinhUB_DungHan_HPT = data1.View_LoaiHS_trinhUB_DungHan_HPTs;
            var Emplist = new List<View_LoaiHS_trinhUB_DungHan_HPT_entry>();
            int count = 0;
            foreach (var data in View_LoaiHS_trinhUB_DungHan_HPT.ToList())
            {

                View_LoaiHS_trinhUB_DungHan_HPT_entry GetEmp = new View_LoaiHS_trinhUB_DungHan_HPT_entry();
                count++;
                GetEmp.Id = count;
                GetEmp.trinhUBND = (int)data.trinhUBND;
                GetEmp.trinhUBND_desc = (string)data.trinhUBND_desc;
                GetEmp.TT_DungHan = (int)data.TT_DungHan;
                GetEmp.TT_DungHan_desc = (string)data.TT_DungHan_desc;
                GetEmp.loai_hs = (int)data.loai_hs;
                GetEmp.loai_hs_desc = (string)data.loai_hs_desc;
                GetEmp.Soluong = (int)data.soluong;
                Emplist.Add(GetEmp);

               
            }
           

            return Emplist;

        }

        //--------------------------------------------------------------------------

        public List<ViewDADT_byloaiDN_HPT_entry> getViewDADT_byloaiDN_HPT()
        {
            var TTTongQuat = data1.ViewDADT_byloaiDN_HPTs;
            var Emplist = new List<ViewDADT_byloaiDN_HPT_entry>();
            foreach (var data in TTTongQuat.ToList())
            {

                ViewDADT_byloaiDN_HPT_entry GetEmp = new ViewDADT_byloaiDN_HPT_entry();
                GetEmp.tenloaidn = (string)data.TenLoaiDN;
                
                GetEmp.SL = (int)data.sl;

                Emplist.Add(GetEmp);

            }
            return Emplist;

        }

        public List<ViewDADT_bymonth_HPT_entry> getViewDADT_bymonth_HPT()
        {
            var TTTongQuat = data1.ViewDADT_bymonth_HPTs;
            var Emplist = new List<ViewDADT_bymonth_HPT_entry>();
            foreach (var data in TTTongQuat.ToList())
            {

                ViewDADT_bymonth_HPT_entry GetEmp = new ViewDADT_bymonth_HPT_entry();
                GetEmp.nam = (int)data.YEAR;
                GetEmp.thang = (int)data.Month;
                GetEmp.sl = (int)data.sl;

                Emplist.Add(GetEmp);

            }
            return Emplist;

        }

        public List<ViewDADT_byTenloaiHinhDT_HPT_entry> getViewDADT_byTenloaiHinhDT_HPT()
        {
            var TTTongQuat = data1.ViewDADT_byTenloaiHinhDT_HPTs;
            var Emplist = new List<ViewDADT_byTenloaiHinhDT_HPT_entry>();
            foreach (var data in TTTongQuat.ToList())
            {

                ViewDADT_byTenloaiHinhDT_HPT_entry GetEmp = new ViewDADT_byTenloaiHinhDT_HPT_entry();
                GetEmp.tenloaihinhDT = (string)data.TenloaiHinhDT;

                GetEmp.SL = (int)data.sl;

                Emplist.Add(GetEmp);

            }
            return Emplist;

        }

        public List<ViewDADT_byTenloaiHinhDT_USD_HPT_entry> getViewDADT_byTenloaiHinhDT_USD_HPT()
        {
            var TTTongQuat = data1.ViewDADT_byTenloaiHinhDT_USD_HPTs;
            var Emplist = new List<ViewDADT_byTenloaiHinhDT_USD_HPT_entry>();
            foreach (var data in TTTongQuat.ToList())
            {

                ViewDADT_byTenloaiHinhDT_USD_HPT_entry GetEmp = new ViewDADT_byTenloaiHinhDT_USD_HPT_entry();
                GetEmp.tenloaihinhDT = (string)data.TenloaiHinhDT;

                GetEmp.SL = (int)data.sl;

                Emplist.Add(GetEmp);

            }
            return Emplist;

        }

        public List<ViewDADT_byTenNganh_HPT_entry> getViewDADT_byTenNganh_HPT()
        {
            var TTTongQuat = data1.ViewDADT_byTenNganh_HPTs;
            var Emplist = new List<ViewDADT_byTenNganh_HPT_entry>();
            foreach (var data in TTTongQuat.ToList())
            {

                ViewDADT_byTenNganh_HPT_entry GetEmp = new ViewDADT_byTenNganh_HPT_entry();
                GetEmp.tennganh = (string)data.TenNganh;

                GetEmp.SL = (int)data.sl;

                Emplist.Add(GetEmp);

            }
            return Emplist;

        }

        public List<ViewDADT_byTenNganh_USD_HPT_entry> getViewDADT_byTenNganh_USD_HPT()
        {
            var TTTongQuat = data1.ViewDADT_byTenNganh_USD_HPTs;
            var Emplist = new List<ViewDADT_byTenNganh_USD_HPT_entry>();
            foreach (var data in TTTongQuat.ToList())
            {

                ViewDADT_byTenNganh_USD_HPT_entry GetEmp = new ViewDADT_byTenNganh_USD_HPT_entry();
                GetEmp.tennganh = (string)data.TenNganh;

                GetEmp.SL = (int)data.sl;

                Emplist.Add(GetEmp);

            }
            return Emplist;

        }

        public List<ViewDADT_byTenQuan_HPT_entry> getViewDADT_byTenQuan_HPT()
        {
            var TTTongQuat = data1.ViewDADT_byTenQuan_HPTs;
            var Emplist = new List<ViewDADT_byTenQuan_HPT_entry>();
            foreach (var data in TTTongQuat.ToList())
            {

                ViewDADT_byTenQuan_HPT_entry GetEmp = new ViewDADT_byTenQuan_HPT_entry();
                GetEmp.tenquan = (string)data.TenQuan;

                GetEmp.SL = (int)data.sl;

                Emplist.Add(GetEmp);

            }
            return Emplist;

        }

        public List<ViewDADT_byTenQuan_USD_HPT_entry> getViewDADT_byTenQuan_USD_HPT()
        {
            var TTTongQuat = data1.ViewDADT_byTenQuan_USD_HPTs;
            var Emplist = new List<ViewDADT_byTenQuan_USD_HPT_entry>();
            foreach (var data in TTTongQuat.ToList())
            {

                ViewDADT_byTenQuan_USD_HPT_entry GetEmp = new ViewDADT_byTenQuan_USD_HPT_entry();
                GetEmp.tenquan = (string)data.TenQuan;

                GetEmp.SL = (int)data.sl;

                Emplist.Add(GetEmp);

            }
            return Emplist;

        }

        public List<ViewDADT_byTenQuocTich_BC_HPT_entry> getViewDADT_byTenQuocTich_BC_HPT()
        {
            var TTTongQuat = data1.ViewDADT_byTenQuocTich_BC_HPTs;
            var Emplist = new List<ViewDADT_byTenQuocTich_BC_HPT_entry>();
            int count = 0;
            foreach (var data in TTTongQuat.ToList())
            {
                count++;
                ViewDADT_byTenQuocTich_BC_HPT_entry GetEmp = new ViewDADT_byTenQuocTich_BC_HPT_entry();
                GetEmp.tenquoctich_BC = (string)data.TenQuocTich_BC;
                GetEmp.Id = count;
                GetEmp.SL = (int)data.sl;

                Emplist.Add(GetEmp);

            }
            return Emplist;

        }

        public List<ViewDADT_byTenQuocTich_BC_USD_HPT_entry> getViewDADT_byTenQuocTich_BC_USD_HPT()
        {
            var TTTongQuat = data1.ViewDADT_byTenQuocTich_BC_USD_HPTs;
            var Emplist = new List<ViewDADT_byTenQuocTich_BC_USD_HPT_entry>();
            foreach (var data in TTTongQuat.ToList())
            {

                ViewDADT_byTenQuocTich_BC_USD_HPT_entry GetEmp = new ViewDADT_byTenQuocTich_BC_USD_HPT_entry();
                GetEmp.tenquoctich_BC = (string)data.TenQuocTich_BC;

                GetEmp.SL = (int)data.sl;

                Emplist.Add(GetEmp);

            }
            return Emplist;

        }

        public List<ViewDADT_top5DAdautu_HPT_entry> getViewDADT_top5DAdautu_HPT()
        {
            var TTTongQuat = data1.view_top5DA_by_vondautuUSD_HPTs;
            var Emplist = new List<ViewDADT_top5DAdautu_HPT_entry>();
        
            foreach (var data in TTTongQuat.ToList())
            {
            
                ViewDADT_top5DAdautu_HPT_entry GetEmp = new ViewDADT_top5DAdautu_HPT_entry();
                GetEmp.Sobnhan = (string)data.socndautu;
                GetEmp.TenDuanDauTu = (string)data.TenDuAnDautu;
                GetEmp.TongvonUSD = (float)data.TongvonUSD;

                Emplist.Add(GetEmp);

            }
            return Emplist;

        }

        //-----------------------------------------------------------------------------------

        //---------------------------------------------------------------------------------------------

        public List<View_HSDangGiaiQuyet_HPT_entry> getView_HSDangGiaiQuyet_HPT()
        {
            var TTTongQuat = data1.View_HSDangGiaiQuyet_HPTs;
            var Emplist = new List<View_HSDangGiaiQuyet_HPT_entry>();
            foreach (var data in TTTongQuat.ToList())
            {

                View_HSDangGiaiQuyet_HPT_entry GetEmp = new View_HSDangGiaiQuyet_HPT_entry();
                GetEmp.type = (int)data.type;
                GetEmp.xuly = (string)data.xuly;
                GetEmp.trangthai_hs = (string)data.trangthai_hs;
                GetEmp.SL = (int)data.soluong;
                GetEmp.typeTT = (int)data.type_ttrang;
                Emplist.Add(GetEmp);

            }
            return Emplist;

        }

        public List<View_HSDangGiaiQuyet_HPT_entry> getView_HSDangGiaiQuyetDetail_HPT(string filter)
        {
            var trinhUB = data1.View_HSDangGiaiQuyet_Detail_HPTs;
            List<View_HSDangGiaiQuyet_HPT_entry> result = trinhUB
                  .Where(row => (row.trangthai_hs.ToString() + row.xuly.ToString()).Equals(filter))
                  .Select(g => new View_HSDangGiaiQuyet_HPT_entry()
                  {
                      sobnhan = g.Sobnhan,
                      trangthai_hs = g.trangthai_hs,
                      xuly= g.xuly,
                      type = g.type,
                      typeTT = g.type_ttrang,
                      ngaynhan = g.NgayNhan.ToString(),
                      ngaytra = g.ngayhtra.ToString(),
                      tenda = g.TenDuAn,
                      tendn = g.TenDN
                  })
                  .ToList();
            return result;
        }

        public List<View_DangGiaiQuyetDetailHS_HPT_entry> getView_DangGiaiQuyetDetailOneHS_HPT(string filter)
        {
            var trinhUB = data1.sp_ThongTinTrangXuLy_hpt(filter);

            List<View_DangGiaiQuyetDetailHS_HPT_entry> result = trinhUB
                  .Select(g => new View_DangGiaiQuyetDetailHS_HPT_entry()
                  {
                      TrangthaiXuly = g.tentinhtrang,
                      Ngayxuly = g.ngayxuly.ToString(),
                      Tennhanvien = g.tenchuyenvien
                  })
                  .ToList();
            return result;
        }


        public List<View_hsDungTreHenHangThang_HPT_entry> getView_hsDungTreHenHangThang_HPT()
        {
            var TTTongQuat = data1.View_hsDungTreHenHangThang_HPTs;
            var Emplist = new List<View_hsDungTreHenHangThang_HPT_entry>();
            foreach (var data in TTTongQuat.ToList())
            {

                View_hsDungTreHenHangThang_HPT_entry GetEmp = new View_hsDungTreHenHangThang_HPT_entry();
                GetEmp.ketqua_xuly = (int)data.ketqua_xyly;
                GetEmp.ketqua_xuly_desc = (string)data.ketqua_xyly_desc;
                GetEmp.SL = (int)data.soluong;
                GetEmp.thang = (int)data.thang;
                GetEmp.nam = (int)data.year;

                Emplist.Add(GetEmp);

            }
            return Emplist;

        }

        #region Detail view
        public List<View_HSDangGiaiQuyet_HPT_entry> getView_ThongTinHSDetail_HPT(int loai_hs, int IsTrinhUB, int IsDungHan)
        {
            var trinhUB = data1.sp_ThongTinHSDetail_hpt(loai_hs,IsTrinhUB,IsDungHan);

            List<View_HSDangGiaiQuyet_HPT_entry> result = trinhUB
                  .Select(g => new View_HSDangGiaiQuyet_HPT_entry()
                  {
                      type = g.loai_hs,
                      typeTT = g.dung_han,
                      trangthai_hs = g.loai_hs_desc,
                      xuly = g.dung_han_desc,
                      sobnhan = g.sobnhan,
                      SL = 1,
                      tenda = g.TenDuAn,
                      tendn = g.TenDN,
                      ngaynhan = g.NgayNhan.ToString(),
                      ngaytra = g.ngayhtra.ToString()

                  })
                  .ToList();
            return result;
        }

        #endregion
        #region GetData ViewTTTongQuatHPTs
        public int GetTongHosoNhan()
        {
            var TTTongQuat = data1.ViewTTTongQuatHPTs;
            var sum = TTTongQuat.Select(c => c.soluong_tt).Sum();
            return (int)sum;
        }

        public int GetDuanHieuLuc()
        {
            var TTTongQuat = data1.View_DA_conhieuluc_HPTs;
            var sum = TTTongQuat.Select(c => c.So_DA).Sum();
            return (int)sum;
        }

        public float GetDuanHieuLuc_usd()
        {
            var TTTongQuat = data1.View_DA_conhieuluc_HPTs;
            var sum = TTTongQuat.Select(c => c.tongvonUSD).Sum();
            return (float)sum;
        }

        public int DoanhNghiepHoatDong()
        {
            var TTTongQuat = data1.View_DA_DN_conhieuluc_HPTs;
            var sum = TTTongQuat.Select(c => c.So_DA).Sum();
            return (int)sum;
        }

        public float DoanhNghiepHoatDong_usd()
        {
            var TTTongQuat = data1.View_DA_DN_conhieuluc_HPTs;
            var sum = TTTongQuat.Select(c => c.tongvonUSD).Sum();
            return (float)sum;
        }


        public int GetDuanGiaiThe()
        {
            var TTTongQuat = data1.View_DA_giaithe_2014_HPTs;
            var sum = TTTongQuat.Select(c => c.So_DA).Sum();
            return (int)sum;
        }

        public float GetDuanGiaithe_usd()
        {
            var TTTongQuat = data1.View_DA_giaithe_2014_HPTs;
            var sum = TTTongQuat.Select(c => c.tongvonUSD).Sum();
            return (float)sum;
        }

        public int GetDuanCapmoi()
        {
            var TTTongQuat = data1.View_DA_Capmoi_2014_HPTs;
            var sum = TTTongQuat.Select(c => c.So_DA).Sum();
            return (int)sum;
        }

        public float GetDuanCapmoi_usd()
        {
            var TTTongQuat = data1.View_DA_Capmoi_2014_HPTs;
            var sum = TTTongQuat.Select(c => c.tongvonUSD).Sum();
            return (float)sum;
        }

        public int GettongDuanbyDN()
        {
            var TTTongQuat = data1.ViewDADT_byloaiDN_HPTs;
            var sum = TTTongQuat.Select(c => c.sl).Sum();
            return (int)sum;
        }

        public int GettongDuanbyDT()
        {
            var TTTongQuat = data1.ViewDADT_byTenloaiHinhDT_HPTs;
            var sum = TTTongQuat.Select(c => c.sl).Sum();
            return (int)sum;
        }

        public float GettongDuanbyDN_usd()
        {
            var TTTongQuat = data1.ViewDADT_byloaiDN_USD_HPTs;
            var sum = TTTongQuat.Select(c => c.TongVonUSD).Sum();
            return (float)sum;
        }

        public float GettongDuanbyDT_usd()
        {
            var TTTongQuat = data1.ViewDADT_byTenloaiHinhDT_USD_HPTs;
            var sum = TTTongQuat.Select(c => c.sl).Sum();
            return (float)sum;
        }

        public int GetHepza()
        {
            var TTTongQuat = data1.View_DonViCapPhep_HPTs;
            var tmp = (from a in TTTongQuat
                       where a.khu.Equals(1)
                       select a.so_DA).Single();
            return (int)tmp.Value;
        }

        public float GetHepza_usd()
        {
            var TTTongQuat = data1.View_DonViCapPhep_HPTs;
            var tmp = (from a in TTTongQuat
                       where a.khu.Equals(1)
                       select a.tongvonUSD).Single();
            return (float)tmp.Value;
        }

        public int GetSHTP()
        {
            var TTTongQuat = data1.View_DonViCapPhep_HPTs;
            var tmp = (from a in TTTongQuat
                       where a.khu.Equals(2)
                       select a.so_DA).Single();
            return (int)tmp.Value;
        }

        public float GetSHTP_usd()
        {
            var TTTongQuat = data1.View_DonViCapPhep_HPTs;
            var tmp = (from a in TTTongQuat
                       where a.khu.Equals(2)
                       select a.tongvonUSD).Single();
            return (float)tmp.Value;
        }

        public int Getothers()
        {
            var TTTongQuat = data1.View_DonViCapPhep_HPTs;
            var tmp = (from a in TTTongQuat
                       where a.khu.Equals(3)
                       select a.so_DA).Single();
            return (int)tmp.Value;
        }

        public float Getothers_usd()
        {
            var TTTongQuat = data1.View_DonViCapPhep_HPTs;
            var tmp = (from a in TTTongQuat
                       where a.khu.Equals(3)
                       select a.tongvonUSD).Single();
            return (float)tmp.Value;
        }

        public int GetGQDungHan()
        {
            var TTTongQuat = data1.ViewTTTongQuatHPTs;
            var tmp = (from a in TTTongQuat
                      where a.Id.Equals(2)
                      select a.soluong_dunghan).Single();
            return (int)tmp.Value;
        }

        public int GetGQTreHan()
        {
            var TTTongQuat = data1.ViewTTTongQuatHPTs;
            var tmp = (from a in TTTongQuat
                       where a.Id.Equals(2)
                       select a.soluong_trehan).Single();
            return (int)tmp.Value;
        }

        public int GetChuaGQQuaHan()
        {
            var TTTongQuat = data1.ViewTTTongQuatHPTs;
            var tmp = (from a in TTTongQuat
                       where a.Id.Equals(1)
                       select a.soluong_trehan).Single();
            return (int)tmp.Value;
        }

        public int GetChuaGQTrongHan()
        {
            var TTTongQuat = data1.ViewTTTongQuatHPTs;
            var tmp = (from a in TTTongQuat
                       where a.Id.Equals(1)
                       select a.soluong_dunghan).Single();
            return (int)tmp.Value;
        }
        #endregion


        #region View_LoaiHS_trinhUB_DungHan_HPT

        public int  GetHStrinhUBND()
        {
            var trinhUB = data1.View_LoaiHS_trinhUB_DungHan_HPTs;
            var tmp = (from a in trinhUB
                       where a.trinhUBND.Equals(1)
                       select a.soluong).Sum();
            return (int)tmp.Value;
        }

        public int GetHSChuatrinhUBND()
        {
            var trinhUB = data1.View_LoaiHS_trinhUB_DungHan_HPTs;
            var tmp = (from a in trinhUB
                       where a.trinhUBND.Equals(0)
                       select a.soluong).Sum();
            return (int)tmp.Value;
        }

        public int GetHStrinhUBND_dagiaiquyet()
        {
            var trinhUB = data1.View_LoaiHS_trinhUB_DungHan_HPTs;
            var tmp = (from a in trinhUB
                       where a.trinhUBND.Equals(1) && a.loai_hs.Equals(1)
                       select a.soluong).Sum();
            return (int)tmp.Value;
        }


        public int GetHStrinhUBND_chuagiaiquyet()
        {
            var trinhUB = data1.View_LoaiHS_trinhUB_DungHan_HPTs;
            var tmp = (from a in trinhUB
                       where a.trinhUBND.Equals(1) && a.loai_hs.Equals(0)
                       select a.soluong).Sum();
            return (int)tmp.Value;
        }

        public int GetHStrinhKHDT()
        {
            var trinhUB = data1.View_LoaiHS_trinhUB_DungHan_HPTs;
            var tmp = (from a in trinhUB
                       where a.trinhUBND.Equals(0)
                       select a.soluong).Sum();
            return (int)tmp.Value;
        }

        public int GetHStrinhKHDT_dagiaiquyet()
        {
            var trinhUB = data1.View_LoaiHS_trinhUB_DungHan_HPTs;
            var tmp = (from a in trinhUB
                       where a.trinhUBND.Equals(0) && a.loai_hs.Equals(1)
                       select a.soluong).Sum();
            return (int)tmp.Value;
        }


        public int GetHStrinhKHDT_chuagiaiquyet()
        {
            var trinhUB = data1.View_LoaiHS_trinhUB_DungHan_HPTs;
            var tmp = (from a in trinhUB
                       where a.trinhUBND.Equals(0) && a.loai_hs.Equals(0)
                       select a.soluong).Sum();
            return (int)tmp.Value;
        }

        public int GetHStrinhUBND_DangGQtre()
        {
            var trinhUB = data1.View_LoaiHS_trinhUB_DungHan_HPTs;
            var tmp = (from a in trinhUB
                       where a.trinhUBND.Equals(1) && a.loai_hs.Equals(0) && a.TT_DungHan.Equals(0)
                       select a.soluong).Sum();
            return (int)tmp.Value;
        }

        public int GetHStrinhUBND_tre()
        {
            var trinhUB = data1.View_LoaiHS_trinhUB_DungHan_HPTs;
            var tmp = (from a in trinhUB
                       where a.trinhUBND.Equals(1) && a.TT_DungHan.Equals(0)
                       select a.soluong).Sum();
            return (int)tmp.Value;
        }

        public int GetHSChuatrinhUBND_tre()
        {
            var trinhUB = data1.View_LoaiHS_trinhUB_DungHan_HPTs;
            var tmp = (from a in trinhUB
                       where a.trinhUBND.Equals(0) && a.TT_DungHan.Equals(0)
                       select a.soluong).Sum();
            return (int)tmp.Value;
        }



        public List<View_TrinhUBND> DagqUBND()
        {
            var trinhUB = data1.View_LoaiHS_trinhUB_DungHan_HPTs;

            List<View_TrinhUBND> result = trinhUB
                  .Where(row => row.loai_hs.Equals(1) && row.trinhUBND.Equals(1))
                  .GroupBy(row => new { row.TT_DungHan_desc })
                  .Select(g => new View_TrinhUBND()
                  {
                      Name = g.Key.TT_DungHan_desc,
                      Soluong = (int)g.Sum(x => x.soluong)
                  })
                  .ToList();
            return result;
        }

        public List<View_TrinhUBND> ChuagqUBND()
        {
            var trinhUB = data1.View_LoaiHS_trinhUB_DungHan_HPTs;

            List<View_TrinhUBND> result = trinhUB
                  .Where(row => row.loai_hs.Equals(0) && row.trinhUBND.Equals(1))
                  .GroupBy(row => new { row.TT_DungHan_desc })
                  .Select(g => new View_TrinhUBND()
                  {
                      Name = g.Key.TT_DungHan_desc,
                      Soluong = (int)g.Sum(x => x.soluong)
                  })
                  .ToList();
            return result;
        }


        public List<View_TrinhUBND> HStrinhUBNDTP()
        {
            var trinhUB = data1.View_LoaiHS_trinhUB_DungHan_HPTs;

            List<View_TrinhUBND> result = trinhUB
                  .Where(row => row.trinhUBND.Equals(1))
                  .GroupBy(row => new { row.TT_DungHan_desc })
                  .Select(g => new View_TrinhUBND()
                  {
                      Name = g.Key.TT_DungHan_desc,
                      Soluong = (int)g.Sum(x => x.soluong)
                  })
                  .ToList();
            return result;
        }

        public List<View_TrinhUBND> HSChuatrinhUBNDTP()
        {
            var trinhUB = data1.View_LoaiHS_trinhUB_DungHan_HPTs;

            List<View_TrinhUBND> result = trinhUB
                  .Where(row => row.trinhUBND.Equals(0))
                  .GroupBy(row => new { row.TT_DungHan_desc })
                  .Select(g => new View_TrinhUBND()
                  {
                      Name = g.Key.TT_DungHan_desc,
                      Soluong = (int)g.Sum(x => x.soluong)
                  })
                  .ToList();
            return result;
        }

        public List<View_TrinhUBND> HsDaGiaiQuyet()
        {
            var trinhUB = data1.View_LoaiHS_trinhUB_DungHan_HPTs;

            List<View_TrinhUBND> result = trinhUB
                  .Where(row => row.loai_hs.Equals(1))
                  .GroupBy(row => new { row.TT_DungHan_desc })
                  .Select(g => new View_TrinhUBND()
                  {
                      Name = g.Key.TT_DungHan_desc,
                      Soluong = (int)g.Sum(x => x.soluong)
                  })
                  .ToList();
            return result;
        }

        public List<View_TrinhUBND> HsDangGiaiQuyet()
        {
            var trinhUB = data1.View_LoaiHS_trinhUB_DungHan_HPTs;

            List<View_TrinhUBND> result = trinhUB
                  .Where(row => row.loai_hs.Equals(0))
                  .GroupBy(row => new { row.TT_DungHan_desc })
                  .Select(g => new View_TrinhUBND()
                  {
                      Name = g.Key.TT_DungHan_desc,
                      Soluong = (int)g.Sum(x => x.soluong)
                  })
                  .ToList();
            return result;
        }

        #endregion

        #region ViewDADT_bymonth_HPT
        public int GetTongDADTthang()
        {
            var TongDADTthang = data1.ViewDADT_bymonth_HPTs;
            var tmp = (from a in TongDADTthang
                       select a.sl).Sum();
            return (int)tmp.Value;
        }
        #endregion


     
        
    }
   
}
